/**
 * Created by jackliu on 15/12/16.
 */

var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbActivityRModel = function(obj){
    ItbBaseModel.call(this,obj);

    // 以下为表定义数据，需要的字段就填入
    //`r_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '关联id (活动商品项目id)',
    //`act_id` bigint(20) NOT NULL COMMENT '活动id',
    //`item_id` bigint(20) NOT NULL COMMENT '商品id',
    //`org_price` bigint(20) NOT NULL COMMENT '原价：单位分',
    //`act_price` bigint(20) NOT NULL COMMENT '活动价：单位分',
    //`pp_id` bigint(20) NOT NULL COMMENT '商品参与活动的套餐id',
    //`qty` bigint(20) NOT NULL COMMENT '商品数量',
    //`rest_qty` bigint(20) NOT NULL COMMENT '剩余商品数量',
    //`desc` text COMMENT '说明',
    //`expiry_time` datetime DEFAULT NULL COMMENT '有效截止日期',
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) DEFAULT NULL,
    //`del_flg` int(1) NOT NULL DEFAULT '0',
};

ItbActivityRModel.prototype = new ItbBaseModel();

// 取得表名
ItbActivityRModel.prototype.getTableName = function(){
    return 'itb_activity_r';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbActivityRModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'r_id='+this.r_id;
};
//------------------------------------------------------------------

//------------------------------------------------------------------
ItbActivityRModel.prototype.getActItemInfoByActId = function(actId, callback) {

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbActivityRModel.getActItemInfoByActId Err:'+err);
            return callback(err, null);
        }

        var sql = " SELECT t.*, "+
            " UNIX_TIMESTAMP(t.expiry_time) AS ts_expiry_time "+
            " FROM itb_activity_r t "+
            " WHERE t.del_flg=0 AND t.act_id="+actId;

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();

            if(result && result.length>0) {
                return callback(error, result);
            } else {
                return callback(error, null);
            }
        });
    });
};

//------------------------------------------------------------------
// 秒杀商品剩余数量-1
ItbActivityRModel.prototype.decrActItemRestQty = function(rId, userId, callback) {

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbActivityRModel.decrActItemRestQty Err:'+err);
            return callback(err, null);
        }

        var sql = " UPDATE itb_activity_r t "+
            " SET t.rest_qty=t.rest_qty-1, t.update_time=sysdate(), t.update_by="+userId+
            " WHERE t.del_flg=0 AND t.r_id="+rId;

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();

            if(error) {
                console.log('ItbActivityRModel.prototype.decrActItemRestQty error:'+error);
                callback(error);
            } else {
                callback(null);
            }
        });
    });
};

//------------------------------------------------------------------
// 检查秒杀商品是否可下单
// actItemId: 秒杀项目id
// return: TRUE/FALSE
ItbActivityRModel.prototype.chkActItemValid = function(actItemId, callback) {

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbActivityRModel.chkActItemValid Err:'+err);
            return callback(err, null);
        }

        var sql = " SELECT t.r_id, a.act_id "+
            " FROM itb_activity_r t, itb_activity a "+
            " WHERE t.del_flg=0 AND t.rest_qty>0 AND t.r_id="+actItemId+// 有剩余
            " AND a.del_flg=0 AND a.publish=1 AND a.act_id=t.act_id "+// 已发布的活动
            " AND now()>=a.start_time AND now()<a.end_time ";// 活动起始时间内

        console.log(sql);
        connection.query(sql, function (error, result) {
            connection.release();
            console.log();

            if(result && result.length>0) {
                return callback(error, true);
            } else {
                return callback(error, false);
            }
        });
    });
};

//------------------------------------------------------------------
// 获取秒杀项目商品价格信息
// 参数: actItemId 秒杀项目id
ItbActivityRModel.prototype.findMinQtyPriceByActItemId = function(actItemId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbActivityRModel.findMinQtyPriceByActItemId Err:'+err);
        }

        // Use the connection
        var sql =
            ' select ar.*, qp.min AS org_min, qp.price AS org_price, qp.unit AS org_unit, min(qp.min) '+
            ' from itb_qty_price qp,  '+
            '      (select qp2.pp_id, min(qp2.min) as valmin from itb_qty_price qp2 where qp2.del_flg=0 group by qp2.pp_id) qp3, '+
            '      itb_print_param pp,  '+
            '      itb_activity_r ar '+
            ' where qp.`del_flg`=0  '+
            ' AND qp.pp_id=qp3.pp_id AND qp.min=qp3.valmin '+// 关联qp3取最小价格
            ' and pp.`del_flg`=0  '+
            ' and ar.`del_flg`=0  '+
            ' and qp.`pp_id`= pp.`pp_id`  '+
            ' and qp.`pp_id`= ar.`pp_id`  '+
            ' and ar.`r_id`= '+ actItemId;


        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(error){
                return callback(error, null);
            }
            if(!result || result.length==0){
                return callback(null,null);
            }

            return callback(error, result[0]);
        });
    });
};

//------------------------------------------------------------------
// 获取秒杀商品信息
ItbActivityRModel.prototype.geActItemInfo = function(actItemId, callback){
    var THIS = this;
    var tableName = this.getTableName();

    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbActivityRModel.geActItemInfo Err:'+err);
            return callback(err,null);
        }
        // Use the connection

        var sql = ' SELECT ar.r_id AS id, '+
            ' ar.org_price AS orgPrice, ar.act_price AS actPrice, '+
            ' ar.qty, ar.rest_qty, '+
            ' ar.item_id, it.item_name AS title, '+
            ' CASE WHEN it.theme_thumb IS NULL OR it.theme_thumb="" '+
            '      THEN func_parse_string_array(it.thumbs,0) '+
            '      ELSE it.theme_thumb '+
            ' END AS thumb, '+
            ' t.top_parent AS typeL, t.parent AS typeM, '+
            ' DATE_FORMAT(ar.expiry_time, "%Y-%m-%d") AS expiryTime '+
            ' FROM '+
            ' itb_activity_r ar ';

        sql += ' INNER JOIN itb_items it '+
            ' ON it.del_flg=0 AND it.item_id=ar.item_id ';

        sql += ' INNER JOIN tg_type t '+
            ' ON t.del_flg=0 AND t.type_id=it.type_id ';

        sql += ' WHERE ar.del_flg=0 AND ar.r_id='+actItemId;

        //var sqlValue = [];
        //var sql = mysql.format(sql, sqlValue);
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();

            console.log();

            if(result && result.length>0) {
                return callback(null, result[0]);
            } else {
                return callback(error, null);
            }
        });
    });
};


//------------------------------------------------------------------
function sql_getActivityItems(bForCnt, id, pageNum, pageSize) {


    var sql1 =
        " SELECT r.r_id AS id, " +
        "        r.item_id as itemId, " +
        "        i.item_name AS title, " +
        "        r.org_price AS orgPrice, " +
        "        r.act_price AS actPrice, " +
        "        r.qty, " +
        "        r.rest_qty AS restQty, " +
        "        CASE WHEN IFNULL(r.rest_qty,0)>0 THEN 1 ELSE 0 END AS flg, " +
        "        r.desc, " +
        "        i.theme_thumb AS thumb, " +
        "        i.thumbs, " +
        "        t.parent AS typeM, " +
        "        t.top_parent AS typeL ";
    var sql2 = " SELECT count(*) AS count ";
    var sql3 =
        " FROM itb_activity_r r, " +
        "      itb_items i, " +
        "      tg_type t " +
        " WHERE r.act_id= " + id +
        "   AND r.del_flg=0 " +
        "   AND i.item_id=r.item_id " +
        "   AND i.del_flg=0 " +
        "   AND i.state=20 " +
        "   AND t.type_id=i.type_id ";

    if(bForCnt) {
        return sql2 + sql3;
    } else {
        var sql = sql1 + sql3+
            " ORDER BY flg DESC, r.create_time DESC ";// 剩余数量为0的排后面
        if (typeof pageNum != 'undefined' && pageNum != null && typeof pageSize != 'undefined' && pageSize != null) {
            var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
            sql += " limit " + skipCnt + "," + Number(pageSize||'0');
        }
        return sql;
    }
}

/**
 * 获取指定场次的秒杀商品列表
 *
 * @param id 场次列表
 * @param pageNum 页码
 * @param pageSize 页容量
 * @param callback
 *          error: 错误
 *          items: 对应列表
 *
 * */
ItbActivityRModel.prototype.getActivityItems = function(id, pageNum, pageSize, callback) {
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function (err, connection) {
        var sql = sql_getActivityItems(false, id, pageNum, pageSize);
        console.log(sql);
        // Use the connection
        connection.query(sql, function (error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });
    });
};
//
ItbActivityRModel.prototype.getActivityItemsCnt = function(id, pageNum, pageSize, callback) {
    var THIS = this;
    // 从mysql中查询
    pool.pool.getConnection(function (err, connection) {
        var sql = sql_getActivityItems(true, id, pageNum, pageSize);
        console.log(sql);
        // Use the connection
        connection.query(sql, function (error, items) {
            // And done with the connection.
            connection.release();
            if(error || items.length <=0) {
                return callback(error, null);
            }
            // 回调
            return callback(error, items[0].count);
        });
    });
};

/**
 * 获取单个秒杀项目的详细信息
 *
 * @param id 秒杀项目 id
 * @param callback
 *          error: 错误新
 *          item : 详细信息
 * */
ItbActivityRModel.prototype.getOneItemDetailInfo = function(id, callback) {
    var THIS = this;

    pool.pool.getConnection(function (err, connection) {
        var sql =
            " SELECT r.r_id AS id, " +
            "        r.item_id, " +
            "        i.item_name AS title, " +
            "        r.org_price AS orgPrice, " +
            "        r.act_price AS actPrice, " +
            "        r.qty, " +
            "        r.rest_qty, " +
            "        r.desc, " +
            "        i.theme_thumb AS thumb, " +
            "        i.thumbs, " +
            "        t.parent AS typeM, " +
            "        t.top_parent AS typeL " +
            " FROM itb_activity_r r, " +
            "      itb_items i, " +
            "      tg_type t " +
            " WHERE r.r_id= " + id +
            "   AND r.del_flg=0 " +
            "   AND i.item_id=r.item_id " +
            "   AND i.del_flg=0 " +
            "   AND i.state=20 " +
            "   AND t.type_id=i.type_id ";
        console.log(sql);
        // Use the connection
        connection.query(sql, function (error, items) {
            // And done with the connection.
            connection.release();
            if(error || items.length <=0) {
                return callback(error, null);
            }
            // 回调
            return callback(error, items[0]);
        });
    });
};

/**
 * 获取指定秒杀项目相同类型的相似项目
 *
 * @param actItemId 秒杀项目唯一标识
 * @param pageNum 页码
 * @param pageSize 页容量
 * @param callback 回调
 *          err: 错误
 *          items: [{}]
 * */

ItbActivityRModel.prototype.getSimilarItems = function(actItemId, pageNum, pageSize, callback) {
    pool.pool.getConnection(function (err, connection) {
        var sql =
            " SELECT r.r_id AS id, " +
            "        r.item_id, " +
            "        i.item_name AS title, " +
            "        r.org_price AS orgPrice, " +
            "        r.act_price AS actPrice, " +
            "        r.qty, " +
            "        r.rest_qty, " +
            "        r.desc, " +
            "        i.theme_thumb AS thumb, " +
            "        i.thumbs, " +
            "        t.parent AS typeM, " +
            "        t.top_parent AS typeL " +
            " FROM itb_activity_r r, " +
            "      itb_items i, " +
            "      tg_type t " +
            " WHERE r.r_id IN " +
            "     (SELECT r.r_id " +
            "      FROM itb_activity_r r " +
            "      WHERE r.act_id = " +
            "          (SELECT r.act_id " +
            "           FROM itb_activity_r r " +
            "           WHERE r.r_id="+actItemId+") " +
            "        AND r.del_flg=0) " +
            "   AND r.del_flg=0 " +
            "   AND i.item_id=r.item_id " +
            "   AND i.del_flg=0 " +
            "   AND i.state=20 " +
            "   AND t.type_id=i.type_id " ;
        if (typeof pageNum != 'undefined' && pageNum != null && typeof pageSize != 'undefined' && pageSize != null) {
            var skipCnt = Number(pageNum||'0')*Number(pageSize||'0');
            sql += " limit " + skipCnt + "," + Number(pageSize||'0');
        }
        console.log(sql);
        // Use the connection
        connection.query(sql, function (error, items) {
            // And done with the connection.
            connection.release();
            return callback(error, items);
        });
    });
};



// 导出
module.exports = ItbActivityRModel;